package ca.ubc.cs304.library.gui.helper;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Date;

import javax.swing.DefaultListModel;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;

import ca.ubc.cs304.library.actions.BorrowerActions;
import ca.ubc.cs304.library.actions.LibrarianActions;
import ca.ubc.cs304.library.actions.ClerkActions;
import ca.ubc.cs304.library.sql.OracleDatabaseWrapper;



	
public class LibExec {

	// Borrower Fields
	private static final String SEARCH_BOOK [] = {"Title", "Main Author", "Subject"};
	private static final String CHECK_ACCOUNT [] = {"SIN or Student Number", "Password"};
	private static final String PLACE_HOLD [] = {"SIN or Student Number", "Call Number", "Password"};
	private static final String PAY_FINE [] = {"SIN or Student Number", "Password", "Fine ID"};
	
	// Librarian Fields	
	private static final String ADD_BOOK [] = {"ISBN", "Title", "Main Author", "Publisher", "Year", "Subject"};	
	private static final String REMOVE_BOOK [] = {"Call Number", "Copy Number"};	
	private static final String REMOVE_BORROWER [] = {"Borrower ID"};
	private static final String GENERATE_REPORT_2 [] = {"Year", "Max Return"};
	
	public static void displayTable (ResultSet rs, JTable table, final JFrame frame, boolean flag1, boolean flag2) throws SQLException {
		DefaultTableModel model = new DefaultTableModel();
		LibTableModel jmodel = new LibTableModel();
		
		if(flag1 == false) table.setModel(model);
		else table.setModel(jmodel);
		


		ResultSetMetaData rsmd = rs.getMetaData();
		int colNo = rsmd.getColumnCount();
		
		for(int i = 1 ; i <= colNo ; i++) {
			if(flag1 == false) model.addColumn(rsmd.getColumnName(i));	
			else jmodel.addColumn(rsmd.getColumnName(i));	
		}
		
		if(flag1 == true) {
			jmodel.addColumn("Email");			
		}
		
		if(flag2 == true){
			model.addColumn("Overdue");
		}
		
		Date date = new Date();
		while(rs.next()) {
			Object[] objects;
			
			if(flag1 == true || flag2 == true) objects = new Object[colNo+1];
			else objects = new Object[colNo];
			for(int i=0;i<colNo;i++){
				objects[i] = rs.getObject(i+1);	
			}
			 
			if(flag2 == true) {
				if(date.after(rs.getDate("DUEDATE"))) {
					objects[objects.length - 1] = new String("Yes");
				} else {
					objects[objects.length - 1] = new String("No");
				}
			}
			 
			if(flag1 == true) objects[objects.length - 1] = new Boolean(false);
			 
			if(flag1 == false) model.addRow(objects);
			else jmodel.addRow(objects);
		}
		
	
		
		jmodel.addTableModelListener(new TableModelListener() {

			@Override
			public void tableChanged(TableModelEvent arg0) {
		        int row = arg0.getFirstRow();
		        int column = arg0.getColumn();
		        LibTableModel model = (LibTableModel)arg0.getSource();
		        //String columnName = model.getColumnName(column);
		        Object data = model.getValueAt(row, column);
		        boolean emailCheck = ((Boolean) data).booleanValue();
		      
		       	if(emailCheck == true) {	
		       		JOptionPane.showMessageDialog(frame,
		       	    "Email has been sent");
		       	}
		   }
		});
		
		int count = table.getColumnModel().getColumnCount();		
        for(int i = 0 ; i < count ; i++) {
        	TableColumn col = table.getColumnModel().getColumn(i);
        	col.setResizable(false);
        }
	}
	
	public boolean checkSubjectText(String subject) {
		return false;
		
	}
	
	
	public static boolean searchBook (JLabel [] labl, JTextField [] text, JTable table, JComboBox list, JFrame frame, OracleDatabaseWrapper sql) throws Exception {
		
		if(table == null) {
			return false;
		}
		
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < SEARCH_BOOK.length - 1; i++) {
			// For searching check for empty parameters is not needed :-)
			// The query takes care of empty input by not restricting the search
			// based on that parameter!
			
			param[i] = text[i].getText().trim();
		}	
		

		if(list.getSelectedItem().equals("")) {
			param[SEARCH_BOOK.length - 1] = "";
		} else {

		param[SEARCH_BOOK.length - 1] = ((String)list.getSelectedItem()).trim();

		}
		

		
		
		// Send Query
		ResultSet rs = BorrowerActions.searchBooks(sql, param[2], param[1], param[0]);
		displayTable(rs, table, frame, false, false);	
		
		
		return true;
	}
	
	public static boolean checkAccount (JLabel [] labl, JTextField [] text,  
			JTable table1, JTable table2, JTable table3, JFrame frame, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < CHECK_ACCOUNT.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}
		
		
		// Send Query	
		BorrowerActions.Status status = BorrowerActions.checkStatus(sql, Integer.parseInt(param[0]), param[1]);
		displayTable(status.booksOut, table1, frame, false, false);
		displayTable(status.holds, table2, frame, false, false);
		displayTable(status.fines, table3, frame, false, false);
		
		
		return true;		
	}
	
	public static boolean placeHold (JLabel [] labl, JTextField [] text,  JTable table, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < PLACE_HOLD.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}	
		
		BorrowerActions.makeHold(sql, param[1], Integer.parseInt(param[0]), param[2]);
		// Send Query
		return true;		
	}
	
	public static boolean payFine (JLabel [] labl, JTextField [] text,  JTable table, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < PAY_FINE.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}	

		// Send Query	
		BorrowerActions.payFine(sql, Integer.parseInt(param[2]), Integer.parseInt(param[0]), param[1]);
		
		return true;		
	}

	// -----------------------------------------------------------------------------------------------------------------------------
	// Librarian Actions
	// -----------------------------------------------------------------------------------------------------------------------------
	public static boolean addBook(JLabel [] labl, JTextField [] text,  JTable table, JList list, DefaultListModel listModel, JFrame frame, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < ADD_BOOK.length - 1; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}	
		
		int [] index = list.getSelectedIndices();
		String [] subj = new String [list.getSelectedIndices().length];
		
		for(int i = 0 ; i < index.length ; i++) {
			subj[i] = listModel.getElementAt(index[i]).toString();
		}
		
		
		// Send Query
		ResultSet rs = LibrarianActions.insertBook(sql, param[0], param[1], param[2], param[3], Integer.parseInt(param[4]), subj);
		//displayTable(rs, table, frame, false, false);
		
		return true;
	}

	public static boolean removeBook (JLabel [] labl, JTextField [] text,  JTable table, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < REMOVE_BOOK.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}	

		// Send Query
		LibrarianActions.deleteBook(sql, Integer.parseInt(param[1]), param[0]);
		return true;
	}
	
	
	public static boolean removeBorrower (JLabel [] labl, JTextField [] text,  JTable table, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < REMOVE_BORROWER.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}	

		// Send Query	
		LibrarianActions.deleteBorrower(sql, Integer.parseInt(param[0]));
		return true;		
	}
	
	public static boolean generateReport1 (JLabel [] labl, JTextField [] text,  JComboBox list, JTable table,JFrame frame,  OracleDatabaseWrapper sql) throws Exception {
		String [] param = new String [labl.length];
		
		param[0] = (String) list.getSelectedItem();
		
		
		ResultSet rs = LibrarianActions.generateCheckedOutReport(sql, param[0]);
		displayTable(rs, table, frame, false, true);
		// Send Query			
		return true;
	}
	
	public static boolean generateReport2 (JLabel [] labl, JTextField [] text, JTable table,  JFrame frame, OracleDatabaseWrapper sql) throws NumberFormatException, Exception {
		String [] param = new String [labl.length];
		
		for(int i = 0 ; i < GENERATE_REPORT_2.length ; i++) {
			if(text[i].getText().equals("")) {
				return false;
			}	
			
			param[i] = text[i].getText();
		}		
		
		ResultSet rs = LibrarianActions.generatePopularReport(sql, Integer.parseInt(param[0]), Integer.parseInt(param[1]));
		
		displayTable(rs, table, frame, false, false);		
		return true;		
	}
	
	
	// -----------------------------------------------------------------------------------------------------------------------------
	// Clerk Actions
	// -----------------------------------------------------------------------------------------------------------------------------
	public static boolean checkOverdue (ClerkActions action, JFrame frame, JTable table) throws SQLException {
		ResultSet rs = action.checkOverdue();
		displayTable(rs, table, frame, true, false);

		return true;		
	}
	
	
	public static void createClerkAddBorrowerLayout(JPanel panelToDisplayedIn, JTextField sinOrStudentNumberTextField, JTextField nameTextField, JPasswordField passwordTextField, JTextField addressTextField, JTextField phoneNumberTextField, JTextField emailAddressTextField, JTextField expiryDateTextField, JComboBox userTypeComboBox) {

	}

	public static void createClerkCheckOutItemLayout(JPanel panelToDisplayedIn, JTextField borrowerIdTextField, JTextField callNumberTextField1, JTextField callNumberTextField2, JTextField callNumberTextField3, JTextField callNumberTextField4, JTextField callNumberTextField5, JTextField callNumberTextField6) {

	}
	
	public static void createClerkProcessReturnLayout(JPanel panelToDisplayedIn, JTextField callNumberTextField1, JTextField callNumberTextField2, JTextField callNumberTextField3, JTextField callNumberTextField4, JTextField callNumberTextField5, JTextField callNumberTextField6) {
	
	}	
}
